package com.sacfa.commons.bo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import org.apache.log4j.Logger;

import com.sacfa.commons.beans.Colonia;
import com.sacfa.commons.beans.Municipio;
import com.sacfa.commons.connection.PoolConexion;
import com.sacfa.commons.sql.ColoniaSQL;
import com.sacfa.commons.utilities.SACFAExceptions;

/**
 * @author Leizar Zar
 * @version 1.0
 * @created 11-ago-2013
 */
public class ColoniaBo {
	private static Logger log = Logger.getLogger(ColoniaBo.class);
	
	public Colonia consultar(Colonia colonia){
		Municipio municipio = new Municipio();
		PreparedStatement stm = null;
		ResultSet rs = null;
		Connection conex = PoolConexion.getInstance().getConexion();
		
		try {
			stm = conex.prepareStatement(ColoniaSQL.SELECT_ONE);
			stm.setString(1, colonia.getDescripcion());
			rs = stm.executeQuery();
			
			rs.next();
			colonia.setId(rs.getInt(1));
			colonia.setDescripcion(rs.getString(2));
			municipio.setId(rs.getInt(3));
			colonia.setMunicipio(municipio);
		} catch(SQLException ex) {
			log.info("Error SQL", ex);
			throw new SACFAExceptions("Error SQL", ex);
		} finally {
			PoolConexion.getInstance().endConection(conex);
		}
		return colonia;
	}
	
	public Collection<Colonia> consultaGeneral(){
		Collection<Colonia> colonias = new ArrayList<Colonia>();
		Colonia colonia = new Colonia();
		Municipio municipio = new Municipio();
		PreparedStatement stm = null;
		ResultSet rs = null;
		Connection conex = PoolConexion.getInstance().getConexion();
		
		try {
			stm = conex.prepareStatement(ColoniaSQL.SELECT_ALL);
			rs = stm.executeQuery();
			
			while(rs.next()){
				colonia.setId(rs.getInt(1));
				colonia.setDescripcion(rs.getString(2));
				municipio.setId(rs.getInt(3));
				colonia.setMunicipio(municipio);
				colonias.add(colonia);
			}
		} catch(SQLException ex) {
			log.info("Error SQL", ex);
			throw new SACFAExceptions("Error SQL", ex);
		} finally {
			PoolConexion.getInstance().endConection(conex);
		}
		return colonias;
	}
	
	public Collection<Colonia> consultaPorMunicipio(Municipio municipio){
		Collection<Colonia> colonias = new ArrayList<Colonia>();
		Colonia colonia = null;
		PreparedStatement stm = null;
		ResultSet rs = null;
		Connection conex = PoolConexion.getInstance().getConexion();
		
		try {
			stm = conex.prepareStatement(ColoniaSQL.SELECT_FOR_MUNICIPIO);
			stm.setInt(1, municipio.getId());
			rs = stm.executeQuery();
			
			while(rs.next()){
				colonia = new Colonia();
				colonia.setId(rs.getInt(1));
				colonia.setMunicipio(municipio);
				colonia.setDescripcion(rs.getString(3));
				colonias.add(colonia);
			}
		} catch(SQLException ex) {
			log.info("Error SQL", ex);
			throw new SACFAExceptions("Error SQL", ex);
		} finally {
			PoolConexion.getInstance().endConection(conex);
		}
		return colonias;
	}
}
